﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using FortuneHeavenCommon.DataMap;
using Jzy.Core.Common;

namespace FortuneHeavenDAL
{
    public class AreaUserScoreDAL : BaseDAL
    {
        public List<AreaUserScore> GetList(int page, int size,string province, string city)
        {
            string condition = "where 1=1 ";
            List<object> content = new List<object>();
            if (!string.IsNullOrEmpty(province))
            {
                condition = condition + " and A.Province = '" + province + "' ";
            }
            if (!string.IsNullOrEmpty(city))
            {
                condition = condition + " and A.City = '" + city + "' ";
            }
            string sql = @"select PlayTimeCount,GameLogonTimes as GameLogonTimes,Accounts,GameID,Add_Area as Area,ChargeValue,Total,GiveScore,(ChargeValue - Total - GiveScore) as GameUsedScore 
 from (
 SELECT C.PlayTimeCount,B.GameLogonTimes ,B.Accounts,B.GameID,('省:'+isnull(A.Province,'无') + ' 市:'+ISNULL(A.City,'无')) as Add_Area ,ROW_NUMBER() OVER(ORDER BY B.UserID DESC) ROW,
 Isnull((select SUM(Price)*50 from FortuneHeaven..GameShowOrders where [USERID] =B.UserID),0 )AS ChargeValue,
 Isnull((select SUm(Score)+SUm(InsureScore) from QPTreasureDB..GameScoreInfo where [USERID] =B.UserID),0) AS Total,
 Isnull((select SUM(jf) from QPGameUserDB..cftt_zsjf where fromuser=B.Accounts),0) As GiveScore
 FROM UserInfor A right join QPGameUserDB..AccountsInfo B 
 on A.UserID = B.UserID
 left join QPTreasureDB..GameScoreInfo C On B.UserID = C.UserID
{0}
) as FF
 WHERE FF.ROW BETWEEN  @p0 AND @p1";
           List<AreaUserScore> rets =  dbOper.Query<AreaUserScore>(string.Format(sql, condition), size * (page - 1) + 1, size * page);
           return rets;
        }
        public List<AreaUserScore> GetList(string province, string city)
        {
            string condition = "where 1=1 ";
            List<object> content = new List<object>();
            if (!string.IsNullOrEmpty(province))
            {
                condition = condition + " and A.Province = '" + province + "' ";
            }
            if (!string.IsNullOrEmpty(city))
            {
                condition = condition + " and A.City = '" + city + "' ";
            }
            string sql = @"select PlayTimeCount,GameLogonTimes as GameLogonTimes,Accounts,GameID,Add_Area as Area,ChargeValue,Total,GiveScore,(ChargeValue - Total - GiveScore) as GameUsedScore 
 from (
 SELECT C.PlayTimeCount,B.GameLogonTimes ,B.Accounts,B.GameID,('省:'+isnull(A.Province,'无') + ' 市:'+ISNULL(A.City,'无')) as Add_Area ,ROW_NUMBER() OVER(ORDER BY B.UserID DESC) ROW,
 Isnull((select SUM(Price)*50 from FortuneHeaven..GameShowOrders where [USERID] =B.UserID),0 )AS ChargeValue,
 Isnull((select SUm(Score)+SUm(InsureScore) from QPTreasureDB..GameScoreInfo where [USERID] =B.UserID),0) AS Total,
 Isnull((select SUM(jf) from QPGameUserDB..cftt_zsjf where fromuser=B.Accounts),0) As GiveScore
 FROM UserInfor A right join QPGameUserDB..AccountsInfo B 
 on A.UserID = B.UserID
 left join QPTreasureDB..GameScoreInfo C On B.UserID = C.UserID
{0}
) as FF";
           List<AreaUserScore> rets =  dbOper.Query<AreaUserScore>(string.Format(sql, condition));
           return rets;
        }

        public int GetTotalSize(string province, string city)
        {
            string condition = "where 1=1 ";
            List<object> content = new List<object>();
            if (!string.IsNullOrEmpty(province))
            {
                condition = condition + " and A.Province = '" + province + "' ";
            }
            if (!string.IsNullOrEmpty(city))
            {
                condition = condition + " and A.City = '" + city + "' ";
            }
            string sql = @"SELECT  COUNT(B.Accounts) FROM UserInfor A right join QPGameUserDB..AccountsInfo B on A.UserID = B.UserID {0}";
            return dbOper.GetScalar<int>(string.Format(sql, condition));
        }



        public List<AreaUserScore> GetMoreDetailList(int page, int size, string province, string city)
        {
            string condition = "where 1=1 ";
            List<object> content = new List<object>();
            if (!string.IsNullOrEmpty(province))
            {
                condition = condition + " and A.Province = '" + province + "' ";
            }
            if (!string.IsNullOrEmpty(city))
            {
                condition = condition + " and A.City = '" + city + "' ";
            }
            string sql = @"select PlayTimeCount,Add_MobilePhone as Mobile, Accounts,GameID,Add_Area as Area, RegisterIP ,RegisterDate,LastLogonDate,GameLogonTimes,Revenue,SpreadValue, ChargeValue,Total,GiveScore,(ChargeValue - Total - GiveScore) as GameUsedScore 
from (
SELECT C.PlayTimeCount,B.Add_MobilePhone, B.Accounts,B.GameID,('省:'+isnull(A.Province,'无') + ' 市:'+ISNULL(A.City,'无')) as Add_Area, B.RegisterIP ,B.RegisterDate,B.LastLogonDate,B.GameLogonTimes,A.SpreadValue
,ROW_NUMBER() OVER(ORDER BY B.UserID DESC) ROW,
 Isnull((select SUM(Price)*50 from FortuneHeaven..GameShowOrders where [USERID] =B.UserID),0 )AS ChargeValue,
 Isnull((select SUm(Score)+SUm(InsureScore) from QPTreasureDB..GameScoreInfo where [USERID] =B.UserID),0) AS Total,
 Isnull((select SUm(Revenue) from QPTreasureDB..GameScoreInfo where [USERID] =B.UserID),0) AS Revenue,
 Isnull((select SUM(jf) from QPGameUserDB..cftt_zsjf where fromuser=B.Accounts),0) As GiveScore
 FROM UserInfor A right join QPGameUserDB..AccountsInfo B on A.UserID = B.UserID
 left join QPTreasureDB..GameScoreInfo C On B.UserID = C.UserID
{0}
) as FF
 WHERE FF.ROW BETWEEN  @p0 AND @p1";
            List<AreaUserScore> rets = dbOper.Query<AreaUserScore>(string.Format(sql, condition), size * (page - 1) + 1, size * page);
            return rets;
        }

        public List<AreaUserScore> GetMoreDetailList(string province, string city)
        {
            string condition = "where 1=1 ";
            List<object> content = new List<object>();
            if (!string.IsNullOrEmpty(province))
            {
                condition = condition + " and A.Province = '" + province + "' ";
            }
            if (!string.IsNullOrEmpty(city))
            {
                condition = condition + " and A.City = '" + city + "' ";
            }
            string sql = @"select PlayTimeCount,Add_MobilePhone as Mobile, Accounts,GameID,Add_Area as Area, RegisterIP ,RegisterDate,LastLogonDate,GameLogonTimes,Revenue,SpreadValue, ChargeValue,Total,GiveScore,(ChargeValue - Total - GiveScore) as GameUsedScore 
from (
SELECT C.PlayTimeCount,B.Add_MobilePhone, B.Accounts,B.GameID,('省:'+isnull(A.Province,'无') + ' 市:'+ISNULL(A.City,'无')) as Add_Area, B.RegisterIP ,B.RegisterDate,B.LastLogonDate,B.GameLogonTimes,A.SpreadValue
,ROW_NUMBER() OVER(ORDER BY B.UserID DESC) ROW,
 Isnull((select SUM(Price)*50 from FortuneHeaven..GameShowOrders where [USERID] =B.UserID),0 )AS ChargeValue,
 Isnull((select SUm(Score)+SUm(InsureScore) from QPTreasureDB..GameScoreInfo where [USERID] =B.UserID),0) AS Total,
 Isnull((select SUm(Revenue) from QPTreasureDB..GameScoreInfo where [USERID] =B.UserID),0) AS Revenue,
 Isnull((select SUM(jf) from QPGameUserDB..cftt_zsjf where fromuser=B.Accounts),0) As GiveScore
 FROM UserInfor A right join QPGameUserDB..AccountsInfo B on A.UserID = B.UserID
 left join QPTreasureDB..GameScoreInfo C On B.UserID = C.UserID
{0}
) as FF
";
            List<AreaUserScore> rets = dbOper.Query<AreaUserScore>(string.Format(sql, condition));
            return rets;
        }

        public int GetMoreTotalSize(string province, string city)
        {
            string condition = "where 1=1 ";
            List<object> content = new List<object>();
            if (!string.IsNullOrEmpty(province))
            {
                condition = condition + " and A.Province = '" + province + "' ";
            }
            if (!string.IsNullOrEmpty(city))
            {
                condition = condition + " and A.City = '" + city + "' ";
            }
            string sql = @"SELECT  COUNT(B.Accounts) FROM UserInfor A right join QPGameUserDB..AccountsInfo B on A.UserID = B.UserID {0}";
            return dbOper.GetScalar<int>(string.Format(sql, condition));
        }
    }
    
}
